package com.andatsoft.laisim.db;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;

import com.andatsoft.laisim.R;
import com.andatsoft.laisim.audioeffect.AudioEffect;
import com.andatsoft.laisim.audioeffect.AudioEffect.ApplyMode;
import com.andatsoft.laisim.entity.AlbumItem;
import com.andatsoft.laisim.entity.Playlist;
import com.andatsoft.laisim.entity.SkinItem;
import com.andatsoft.laisim.entity.Song;
import com.andatsoft.laisim.entity.ThemeItem;
import com.andatsoft.laisim.entity.TwoLineItem;

public class DataBaseAccess extends SQLiteOpenHelper {

	private static DataBaseAccess mDataBaseAccess;
	private static SQLiteDatabase mSQLiteDb;

	private static final String DB_NAME = "lsdb";
	private static final int DB_VERSION = 1;

	private static final String TABLE_SONGS = "tb_song";
	private static final String TABLE_SONGS_SAVED = "tb_saved_playing_song";
	private static final String TABLE_LYRICS = "tb_lyrics";
	private static final String TABLE_PLAYLIST = "tb_playlist";
	private static final String TABLE_PLAYLIST_DETAIL = "tb_playlist_detail";

	private static final String TABLE_AUDIO_EFFECT = "tb_ae";

	private static final String TABLE_THEME = "tb_theme";
	private static final String TABLE_SKIN = "tb_skin";
	private static final String TABLE_ALBUM_ART = "tb_album_art";
	private Context mContext;

	public static synchronized void initDatabase(Context cxt) {
		if (mDataBaseAccess == null) {
			mDataBaseAccess = new DataBaseAccess(cxt.getApplicationContext());
			mSQLiteDb = mDataBaseAccess.getWritableDatabase();
		}
	}

	public static DataBaseAccess getInstance(Context cxt) {
		if (mDataBaseAccess == null)
			initDatabase(cxt);
		return mDataBaseAccess;
	}

	public static synchronized SQLiteDatabase getDatabase(Context cxt) {
		if (mDataBaseAccess == null)
			initDatabase(cxt);
		return mSQLiteDb;
	}

	public static synchronized void closeDatabase() {
		if (mSQLiteDb != null) {
			mSQLiteDb.close();
		}
		if (mDataBaseAccess != null) {
			mDataBaseAccess.close();
			mDataBaseAccess = null;
		}
	}

	private DataBaseAccess(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		this.mContext = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// Create table songs
		String sql = "CREATE TABLE "
				+ TABLE_SONGS
				+ "(song_id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "song_address NVARCHAR(75),"
				+ "song_title NVARCHAR(50),"
				+ "song_album NVARCHAR(30),"
				+ "song_artist NVARCHAR(20),"
				+ "song_genre NVARCHAR(20),"
				+ "song_bitrate INTEGER,"
				+ "is_favorite INTEGER,"
				+ "played_time INTEGER,"
				+ "song_duration INTEGER,song_folder NVARCHAR(50), effect_id INTEGER)";
		db.execSQL(sql);

		// Create table saved songs
		sql = "CREATE TABLE " + TABLE_SONGS_SAVED + "(song_id INTEGER)";
		db.execSQL(sql);

		// Create table lyrics
		sql = "CREATE TABLE " + TABLE_LYRICS + "(song_id INTEGER,"
				+ "lyrics NVARCHAR(1000))";
		db.execSQL(sql);
		// Create table playlist
		sql = "CREATE TABLE " + TABLE_PLAYLIST
				+ "(playlist_id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "playlist_name NVARCHAR(50)," + "is_playing INTEGER,"
				+ "playing_song_id INTEGER)";
		db.execSQL(sql);
		// Create table playlist detail
		sql = "CREATE TABLE " + TABLE_PLAYLIST_DETAIL + "(playlist_id INTEGER,"
				+ "song_id INTEGER)";
		db.execSQL(sql);
		// Create table theme
		sql = "CREATE TABLE "
				+ TABLE_THEME
				+ "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "name NVARCHAR(50), author NVARCHAR(50), address NVARCHAR(64), is_selected INTEGER)";
		db.execSQL(sql);

		// Create table theme
		sql = "CREATE TABLE "
				+ TABLE_SKIN
				+ "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "name NVARCHAR(50), author NVARCHAR(50),address NVARCHAR(64), is_selected INTEGER)";
		db.execSQL(sql);

		// Create table audio effect
		sql = "CREATE TABLE "
				+ TABLE_AUDIO_EFFECT
				+ "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "name NVARCHAR(50), eq_60 INTEGER, eq_230 INTEGER,"
				+ "eq_910 INTEGER,eq_3K6 INTEGER,eq_14K INTEGER,bass_strength INTEGER,"
				+ "volume INTEGER, balance INTEGER, is_eq INTEGER, is_bass INTEGER, am INTEGER,"
				+ "can_delete_when_apply_all INTEGER)";
		db.execSQL(sql);
		// Create table album art
		sql = "CREATE TABLE " + TABLE_ALBUM_ART + "(song_id INTEGER ,"
				+ "address NVARCHAR(128), next_index INTEGER, is_show INTEGER)";
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	private synchronized void deleteAlbumArt() {
		File f = new File(Environment.getExternalStorageDirectory()
				+ "/Laisim/AlbumArt/");
		if (f.exists()) {
			// get all content in this folder
			File[] fContents = f.listFiles();
			// delete all content
			for (int i = 0; i < fContents.length; i++) {
				if (fContents[i].isFile()) {
					fContents[i].delete();
				}
			}
		}
	}

	public synchronized void upgradeWhenDoFullScan() {
		deleteAlbumArt();
		mSQLiteDb.execSQL("DROP TABLE IF EXISTS " + TABLE_SONGS);
		mSQLiteDb.execSQL("DROP TABLE IF EXISTS " + TABLE_SONGS_SAVED);
		mSQLiteDb.execSQL("DROP TABLE IF EXISTS " + TABLE_AUDIO_EFFECT);
		mSQLiteDb.execSQL("DROP TABLE IF EXISTS " + TABLE_LYRICS);
		mSQLiteDb.execSQL("DROP TABLE IF EXISTS " + TABLE_PLAYLIST);
		mSQLiteDb.execSQL("DROP TABLE IF EXISTS " + TABLE_PLAYLIST_DETAIL);
		mSQLiteDb.execSQL("DROP TABLE IF EXISTS " + TABLE_ALBUM_ART);

		// Create table songs
		String sql = "CREATE TABLE "
				+ TABLE_SONGS
				+ "(song_id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "song_address NVARCHAR(75),"
				+ "song_title NVARCHAR(50),"
				+ "song_album NVARCHAR(30),"
				+ "song_artist NVARCHAR(20),"
				+ "song_genre NVARCHAR(20),"
				+ "song_bitrate INTEGER,"
				+ "is_favorite INTEGER,"
				+ "played_time INTEGER,"
				+ "song_duration INTEGER,song_folder NVARCHAR(50), effect_id INTEGER)";
		mSQLiteDb.execSQL(sql);

		// Create table saved songs
		sql = "CREATE TABLE " + TABLE_SONGS_SAVED + "(song_id INTEGER)";
		mSQLiteDb.execSQL(sql);

		// Create table lyrics
		sql = "CREATE TABLE " + TABLE_LYRICS + "(song_id INTEGER,"
				+ "lyrics NVARCHAR(1000))";
		mSQLiteDb.execSQL(sql);
		// Create table playlist
		sql = "CREATE TABLE " + TABLE_PLAYLIST
				+ "(playlist_id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "playlist_name NVARCHAR(50)," + "is_playing INTEGER,"
				+ "playing_song_id INTEGER)";
		mSQLiteDb.execSQL(sql);
		// Create table playlist detail
		sql = "CREATE TABLE " + TABLE_PLAYLIST_DETAIL + "(playlist_id INTEGER,"
				+ "song_id INTEGER)";
		mSQLiteDb.execSQL(sql);

		// Create table audio effect
		sql = "CREATE TABLE "
				+ TABLE_AUDIO_EFFECT
				+ "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "name NVARCHAR(50), eq_60 INTEGER, eq_230 INTEGER,"
				+ "eq_910 INTEGER,eq_3K6 INTEGER,eq_14K INTEGER,bass_strength INTEGER,"
				+ "volume INTEGER, balance INTEGER, is_eq INTEGER, is_bass INTEGER, am INTEGER,"
				+ "can_delete_when_apply_all INTEGER)";
		mSQLiteDb.execSQL(sql);
		// Create table album art
		sql = "CREATE TABLE " + TABLE_ALBUM_ART + "(song_id INTEGER ,"
				+ "address NVARCHAR(128), next_index INTEGER, is_show INTEGER)";
		mSQLiteDb.execSQL(sql);
	}

	public synchronized boolean addSong(Song song) {
		if (isSongExist(song.getAddress())) {
			return false;
		}
		ContentValues values = new ContentValues();
		values.put("song_address", song.getAddress());
		values.put("song_title", song.getTitle());
		values.put("song_album", song.getAlbum());
		values.put("song_artist", song.getArtist());
		values.put("song_genre", song.getGenre());
		values.put("song_bitrate", song.getBitRate());
		values.put("is_favorite", song.isFavorite());
		values.put("played_time", song.getPlayedTime());
		values.put("song_duration", song.getDuration());
		values.put("song_folder", song.getFolder());
		values.put("effect_id", song.getEffectId());

		// Inserting Row
		long re = mSQLiteDb.insert(TABLE_SONGS, null, values);
		if (re == -1)
			return false;
		return true;
	}

	private boolean isSongExist(String address) {
		address = address.replace("'", "\'");
		String sql = "select song_id from " + TABLE_SONGS
				+ " where song_address=?";
		Cursor c = null;
		try {
			c = mSQLiteDb.rawQuery(sql, new String[] { address });
			if (c != null && c.getCount() > 0)
				return true;
			return false;
		} catch (Exception e) {
			return false;
		} finally {
			if (c != null)
				c.close();
		}
	}

	public synchronized void deleteSong(int songId) {
		mSQLiteDb.delete(TABLE_SONGS, "song_id=?",
				new String[] { String.valueOf(songId) });
	}

	public synchronized void updateSong(Song song) {

		ContentValues values = new ContentValues();
		values.put("song_address", song.getAddress());
		values.put("song_title", song.getTitle());
		values.put("song_album", song.getAlbum());
		values.put("song_artist", song.getArtist());
		values.put("song_genre", song.getGenre());
		values.put("song_bitrate", song.getBitRate());
		values.put("is_favorite", song.isFavorite());
		values.put("played_time", song.getPlayedTime());
		values.put("song_duration", song.getDuration());
		values.put("song_folder", song.getFolder());
		values.put("effect_id", song.getEffectId());
		mSQLiteDb.update(TABLE_SONGS, values, "song_id=?",
				new String[] { String.valueOf(song.getId()) });
	}

	public Song getSong(int songId) {
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.query(TABLE_SONGS, new String[] { "song_id",
					"song_address", "song_title", "song_album", "song_artist",
					"song_genre", "song_bitrate", "is_favorite", "played_time",
					"song_duration", "effect_id" }, "song_id=?",
					new String[] { String.valueOf(songId) }, null, null, null);
			if (cursor != null)
				cursor.moveToFirst();
			boolean isFav = cursor.getInt(7) == 1 ? true : false;

			Song s = new Song(mContext, cursor.getInt(0), cursor.getString(1),
					cursor.getString(2), cursor.getString(3),
					cursor.getString(4), cursor.getString(5), cursor.getInt(6),
					isFav, cursor.getInt(8), cursor.getInt(9),
					cursor.getInt(10));
			return s;
		} catch (Exception e) {
			return null;
		} finally {
			if (cursor != null)
				cursor.close();
		}

	}

	public synchronized List<Song> getAllSong() {
		String query = "select * from " + TABLE_SONGS
				+ " order by song_title asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<Song> songs = new ArrayList<Song>();
					Song s = null;
					do {
						boolean isFav = cursor.getInt(7) == 1 ? true : false;
						s = new Song(mContext, cursor.getInt(0),
								cursor.getString(1), cursor.getString(2),
								cursor.getString(3), cursor.getString(4),
								cursor.getString(5), cursor.getInt(6), isFav,
								cursor.getInt(8), cursor.getInt(9),
								cursor.getInt(11));
						songs.add(s);
					} while (cursor.moveToNext());
					return songs;
				}
			}
		} catch (Exception e) {
			return new ArrayList<Song>();
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<Song>();
	}

	public synchronized List<Song> getAllSongByRawQuery(String where,
			String groupBy, String orderBy) {
		String query = "select * from " + TABLE_SONGS + " " + where + " "
				+ groupBy + " " + orderBy + " asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<Song> songs = new ArrayList<Song>();
					Song s = null;
					do {
						boolean isFav = cursor.getInt(7) == 1 ? true : false;

						s = new Song(mContext, cursor.getInt(0),
								cursor.getString(1), cursor.getString(2),
								cursor.getString(3), cursor.getString(4),
								cursor.getString(5), cursor.getInt(6), isFav,
								cursor.getInt(8), cursor.getInt(9),
								cursor.getInt(11));
						songs.add(s);
					} while (cursor.moveToNext());
					return songs;
				}
			}

		} catch (Exception e) {
			return new ArrayList<Song>();
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<Song>();
	}

	public synchronized List<Song> getFavoriteSongs() {

		String query = "select * from " + TABLE_SONGS + " "
				+ " where is_favorite =? order by song_title asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query,
					new String[] { String.valueOf(1) });
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<Song> songs = new ArrayList<Song>();
					Song s = null;
					do {
						boolean isFav = cursor.getInt(7) == 1 ? true : false;

						s = new Song(mContext, cursor.getInt(0),
								cursor.getString(1), cursor.getString(2),
								cursor.getString(3), cursor.getString(4),
								cursor.getString(5), cursor.getInt(6), isFav,
								cursor.getInt(8), cursor.getInt(9),
								cursor.getInt(11));
						songs.add(s);
					} while (cursor.moveToNext());
					return songs;
				}
			}
		} catch (Exception e) {
			return new ArrayList<Song>();
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<Song>();
	}

	public synchronized List<Song> getSongsHasLyrics() {
		String query = "select tb_song.song_id, song_address,song_title,"
				+ "song_album,song_artist, song_genre, song_bitrate,"
				+ " is_favorite, played_time,song_duration, effect_id from "
				+ TABLE_SONGS + " inner join " + TABLE_LYRICS
				+ " on tb_song.song_id=tb_lyrics.song_id "
				+ " order by song_title asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {

				if (cursor.moveToFirst()) {
					List<Song> songs = new ArrayList<Song>();
					Song s = null;
					do {
						boolean isFav = cursor.getInt(7) == 1 ? true : false;

						s = new Song(mContext, cursor.getInt(0),
								cursor.getString(1), cursor.getString(2),
								cursor.getString(3), cursor.getString(4),
								cursor.getString(5), cursor.getInt(6), isFav,
								cursor.getInt(8), cursor.getInt(9),
								cursor.getInt(10));
						songs.add(s);
					} while (cursor.moveToNext());
					return songs;
				}
			}
		} catch (Exception e) {
			return new ArrayList<Song>();
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<Song>();
	}

	public synchronized boolean isSongHasLyrics(int songId) {
		String query = "select song_id from " + TABLE_LYRICS
				+ " where song_id=" + songId;
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.getCount() > 0)
					return true;
				else
					return false;
			}
		} catch (Exception e) {
			return false;
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return false;
	}

	public synchronized List<Song> getMostPlayedSongs() {

		String query = "select * from " + TABLE_SONGS + " "
				+ " where played_time >=? order by played_time desc"
				+ " limit 20 ";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query,
					new String[] { String.valueOf(1) });
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<Song> songs = new ArrayList<Song>();
					Song s = null;
					do {
						boolean isFav = cursor.getInt(7) == 1 ? true : false;

						s = new Song(mContext, cursor.getInt(0),
								cursor.getString(1), cursor.getString(2),
								cursor.getString(3), cursor.getString(4),
								cursor.getString(5), cursor.getInt(6), isFav,
								cursor.getInt(8), cursor.getInt(9),
								cursor.getInt(11));
						songs.add(s);
					} while (cursor.moveToNext());
					return songs;
				}
			}
		} catch (Exception e) {
			return new ArrayList<Song>();
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<Song>();
	}

	public synchronized List<Song> getSongsByFolder(String folder) {
		String query = "select * from " + TABLE_SONGS + " "
				+ " where song_folder like '" + folder
				+ "%' order by song_title asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {

				if (cursor.moveToFirst()) {
					List<Song> songs = new ArrayList<Song>();
					Song s = null;
					do {
						boolean isFav = cursor.getInt(7) == 1 ? true : false;

						s = new Song(mContext, cursor.getInt(0),
								cursor.getString(1), cursor.getString(2),
								cursor.getString(3), cursor.getString(4),
								cursor.getString(5), cursor.getInt(6), isFav,
								cursor.getInt(8), cursor.getInt(9),
								cursor.getInt(11));
						songs.add(s);
					} while (cursor.moveToNext());

					return songs;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null)
				cursor.close();
		}

		return new ArrayList<Song>();
	}

	public synchronized List<Song> getSongByArtist(String artist) {

		String query = "select * from " + TABLE_SONGS
				+ " where song_artist=? order by song_title asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, new String[] { artist });
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<Song> songs = new ArrayList<Song>();
					Song s = null;
					do {
						boolean isFav = cursor.getInt(7) == 1 ? true : false;

						s = new Song(mContext, cursor.getInt(0),
								cursor.getString(1), cursor.getString(2),
								cursor.getString(3), cursor.getString(4),
								cursor.getString(5), cursor.getInt(6), isFav,
								cursor.getInt(8), cursor.getInt(9),
								cursor.getInt(11));
						songs.add(s);
					} while (cursor.moveToNext());

					return songs;
				}
			}

		} catch (Exception e) {
			return new ArrayList<Song>();
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<Song>();
	}

	public synchronized List<Song> getSongByGenre(String genre) {

		String query = "select * from " + TABLE_SONGS
				+ " where song_genre=? order by song_title asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, new String[] { genre });
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<Song> songs = new ArrayList<Song>();
					Song s = null;
					do {
						boolean isFav = cursor.getInt(7) == 1 ? true : false;

						s = new Song(mContext, cursor.getInt(0),
								cursor.getString(1), cursor.getString(2),
								cursor.getString(3), cursor.getString(4),
								cursor.getString(5), cursor.getInt(6), isFav,
								cursor.getInt(8), cursor.getInt(9),
								cursor.getInt(11));
						songs.add(s);
					} while (cursor.moveToNext());

					return songs;
				}
			}

		} catch (Exception e) {
			return new ArrayList<Song>();
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<Song>();
	}

	public synchronized List<Song> getSongByPlaylist(int playListId) {
		String query = "select tb_song.song_id, song_address,song_title,"
				+ "song_album,song_artist, song_genre, song_bitrate,"
				+ " is_favorite, played_time,song_duration, effect_id from "
				+ TABLE_SONGS + " inner join " + TABLE_PLAYLIST_DETAIL
				+ " on tb_song.song_id=tb_playlist_detail.song_id "
				+ " where playlist_id=" + playListId
				+ " order by song_title asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<Song> songs = new ArrayList<Song>();
					Song s = null;
					do {
						boolean isFav = cursor.getInt(7) == 1 ? true : false;

						s = new Song(mContext, cursor.getInt(0),
								cursor.getString(1), cursor.getString(2),
								cursor.getString(3), cursor.getString(4),
								cursor.getString(5), cursor.getInt(6), isFav,
								cursor.getInt(8), cursor.getInt(9),
								cursor.getInt(10));
						songs.add(s);
					} while (cursor.moveToNext());
					return songs;
				}
			}
		} catch (Exception e) {
			return new ArrayList<Song>();
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<Song>();
	}

	public synchronized String getSongLyrics(int songId) {

		Cursor cursor = null;
		String str = "";
		try {
			cursor = mSQLiteDb.query(TABLE_LYRICS, new String[] { "lyrics" },
					"song_id=?", new String[] { String.valueOf(songId) }, null,
					null, null);
			str = mContext.getString(R.string.mpsl_lyrics_empty);
			if (cursor == null)
				return mContext.getString(R.string.mpsl_lyrics_empty);
			if (cursor.getCount() <= 0) {
				return mContext.getString(R.string.mpsl_lyrics_empty);
			}
			cursor.moveToFirst();
			if (!cursor.getString(0).equals(""))
				str = cursor.getString(0);
		} catch (Exception e) {
			return str;
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return str;
	}

	private synchronized void addLyrics(int songId, String lyrics) {

		ContentValues values = new ContentValues();
		values.put("song_id", songId);
		values.put("lyrics", lyrics);

		// Inserting Row
		mSQLiteDb.insert(TABLE_LYRICS, null, values);
	}

	public synchronized void updateLyrics(int songId, String lyrics) {

		ContentValues values = new ContentValues();
		values.put("song_id", songId);
		values.put("lyrics", lyrics);

		int re = mSQLiteDb.update(TABLE_LYRICS, values, "song_id=?",
				new String[] { String.valueOf(songId) });
		if (re <= 0)// now rows
		{
			addLyrics(songId, lyrics);
		}
	}

	public synchronized void deleteLyrics(int songId) {

		mSQLiteDb.delete(TABLE_LYRICS, "song_id=?",
				new String[] { String.valueOf(songId) });
	}

	public synchronized void updateFavorite(int songId, int value) {

		ContentValues values = new ContentValues();
		values.put("is_favorite", value);

		mSQLiteDb.update(TABLE_SONGS, values, "song_id=?",
				new String[] { String.valueOf(songId) });
	}

	public synchronized void updateSongPlayedTime(int songId, int value) {
		try {
			ContentValues values = new ContentValues();
			values.put("played_time", value);

			mSQLiteDb.update(TABLE_SONGS, values, "song_id=?",
					new String[] { String.valueOf(songId) });
		} catch (Exception e) {

		}
	}

	public synchronized List<AlbumItem> getAllAlbum() {
		String query = "select song_album, song_artist from " + TABLE_SONGS
				+ " "
				+ " group by song_album, song_artist order by song_album asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {

				if (cursor.moveToFirst()) {
					List<AlbumItem> albums = new ArrayList<AlbumItem>();
					AlbumItem a = null;
					do {
						a = new AlbumItem();
						a.name = cursor.getString(0);
						a.artist = cursor.getString(1);
						albums.add(a);
					} while (cursor.moveToNext());
					return albums;
				}
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<AlbumItem>();
	}

	public synchronized List<AlbumItem> getAlbumByArtist(String artist) {
		String query = "select song_album, song_artist from "
				+ TABLE_SONGS
				+ " where song_artist=? group by song_album order by song_album asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, new String[] { artist });
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<AlbumItem> albums = new ArrayList<AlbumItem>();
					AlbumItem a = null;
					do {
						a = new AlbumItem();
						a.name = cursor.getString(0);
						a.artist = cursor.getString(1);
						albums.add(a);
					} while (cursor.moveToNext());
					return albums;
				}
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<AlbumItem>();
	}

	public synchronized List<AlbumItem> getAlbumByGenre(String genre) {
		String query = "select song_album, song_artist, song_genre from "
				+ TABLE_SONGS
				+ " where song_genre=? group by song_album, song_artist order by song_album asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, new String[] { genre });
			if (cursor != null) {

				if (cursor.moveToFirst()) {
					List<AlbumItem> albums = new ArrayList<AlbumItem>();
					AlbumItem a = null;
					do {
						a = new AlbumItem();
						a.name = cursor.getString(0);
						a.artist = cursor.getString(1);
						a.genre = cursor.getString(2);
						albums.add(a);
					} while (cursor.moveToNext());
					return albums;
				}
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<AlbumItem>();
	}

	public synchronized List<Song> getSongsFromAlbum(String album, String artist) {
		String query = "select * from "
				+ TABLE_SONGS
				+ " "
				+ " where song_album=? and song_artist=? order by song_title asc";
		if (album == null)
			album = "";
		if (artist == null)
			artist = "";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, new String[] { album, artist });
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<Song> songs = new ArrayList<Song>();
					Song s = null;
					do {
						boolean isFav = cursor.getInt(7) == 1 ? true : false;

						s = new Song(mContext, cursor.getInt(0),
								cursor.getString(1), cursor.getString(2),
								cursor.getString(3), cursor.getString(4),
								cursor.getString(5), cursor.getInt(6), isFav,
								cursor.getInt(8), cursor.getInt(9),
								cursor.getInt(11));
						songs.add(s);
					} while (cursor.moveToNext());
					return songs;
				}
			}
		} catch (Exception e) {
			return new ArrayList<Song>();
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<Song>();
	}

	public synchronized List<Song> getSongsFromAlbumAndGenre(String album,
			String artist, String genre) {
		String query = "select * from "
				+ TABLE_SONGS
				+ " where song_album=? and song_artist=? and song_genre=? order by song_title asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, new String[] { album, artist,
					genre });
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<Song> songs = new ArrayList<Song>();
					Song s = null;
					do {
						boolean isFav = cursor.getInt(7) == 1 ? true : false;

						s = new Song(mContext, cursor.getInt(0),
								cursor.getString(1), cursor.getString(2),
								cursor.getString(3), cursor.getString(4),
								cursor.getString(5), cursor.getInt(6), isFav,
								cursor.getInt(8), cursor.getInt(9),
								cursor.getInt(11));
						songs.add(s);
					} while (cursor.moveToNext());
					return songs;
				}
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<Song>();
	}

	public synchronized List<TwoLineItem> getAllArtist() {
		String query = "select song_artist, count(*) from " + TABLE_SONGS
				+ " group by song_artist order by song_artist asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<TwoLineItem> items = new ArrayList<TwoLineItem>();
					TwoLineItem i = null;
					do {
						i = new TwoLineItem();
						i.name = cursor.getString(0);
						i.count = cursor.getInt(1);
						items.add(i);
					} while (cursor.moveToNext());
					return items;
				}
			}
		} catch (Exception e) {
			return new ArrayList<TwoLineItem>();
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<TwoLineItem>();
	}

	public synchronized List<TwoLineItem> getAllGenre() {
		String query = "select song_genre, count(*) from " + TABLE_SONGS
				+ " group by song_genre order by song_genre asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<TwoLineItem> items = new ArrayList<TwoLineItem>();
					TwoLineItem i = null;
					do {
						i = new TwoLineItem();
						i.name = cursor.getString(0);
						i.count = cursor.getInt(1);
						items.add(i);
					} while (cursor.moveToNext());
					return items;
				}
			}
		} catch (Exception e) {
			return new ArrayList<TwoLineItem>();
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<TwoLineItem>();
	}

	private synchronized int getPlaylistId() {
		String query = "select seq from sqlite_sequence where name=" + "'"
				+ TABLE_PLAYLIST + "'";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {

				if (cursor.moveToFirst()) {
					return cursor.getInt(0);
				}
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return 0;
	}

	public synchronized boolean addPlaylist(Playlist playlist) {
		if (isPlaylistExist(playlist.getName()))
			return false;

		ContentValues values = new ContentValues();

		// Add play list
		values.put("playlist_name", playlist.getName());
		values.put("is_playing", 0);
		values.put("playing_song_id", 0);
		mSQLiteDb.insert(TABLE_PLAYLIST, null, values);
		int id = getPlaylistId();
		// add play list details
		for (Song song : playlist.getSongList()) {
			values = new ContentValues();
			values.put("playlist_id", id);
			values.put("song_id", song.getId());
			mSQLiteDb.insert(TABLE_PLAYLIST_DETAIL, null, values);
		}
		return true;
	}

	private synchronized boolean isPlaylistExist(String name) {
		String query = "select * from " + TABLE_PLAYLIST
				+ " where playlist_name= '" + name + "'";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					return true;
				}
			}
			return false;
		} catch (Exception e) {
			return false;
		} finally {
			if (cursor != null)
				cursor.close();
		}
	}

	public synchronized int getPlayingPlaylistId() {
		String query = "select playlist_id from " + TABLE_PLAYLIST
				+ " where is_playing=" + 1;
		Cursor cursor = null;
		int val = -1;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {

				if (cursor.moveToFirst()) {
					val = cursor.getInt(0);
				}
			}
			return val;
		} catch (Exception e) {
			return val;
		} finally {
			if (cursor != null)
				cursor.close();
		}
	}

	public synchronized boolean removeSong(int songId) {
		// first, delete in all song
		mSQLiteDb.delete(TABLE_SONGS, "song_id=?",
				new String[] { String.valueOf(songId) });
		mSQLiteDb.delete(TABLE_LYRICS, "song_id=?",
				new String[] { String.valueOf(songId) });
		removeSongFromPlaylist(songId);

		mSQLiteDb.delete(TABLE_SONGS_SAVED, "song_id=?",
				new String[] { String.valueOf(songId) });
		return false;
	}

	private synchronized int removeSongFromPlaylist(int songId) {
		mSQLiteDb.delete(TABLE_PLAYLIST_DETAIL, "song_id=?",
				new String[] { String.valueOf(songId) });
		int playlistId = getPlayingPlaylistId();
		if (isAllSongRemoved(playlistId)) {
			mSQLiteDb.delete(TABLE_PLAYLIST, "playlist_id=?",
					new String[] { String.valueOf(playlistId) });
			return -1;
		}
		return 1;
	}

	public synchronized int removeSongFromPlaylist(int playlistId, int songId) {
		mSQLiteDb.delete(TABLE_PLAYLIST_DETAIL, "song_id=?",
				new String[] { String.valueOf(songId) });
		if (isAllSongRemoved(playlistId)) {
			mSQLiteDb.delete(TABLE_PLAYLIST, "playlist_id=?",
					new String[] { String.valueOf(playlistId) });
			return -1;
		}
		return 1;
	}

	private synchronized boolean isAllSongRemoved(int playId) {
		String query = "select * from " + TABLE_PLAYLIST_DETAIL
				+ " where playlist_id=" + playId;
		Cursor cursor = null;
		boolean re = false;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {

				if (cursor.getCount() <= 0) {
					re = true;
				}
			}
			return re;
		} catch (Exception e) {
			return re;
		} finally {
			if (cursor != null)
				cursor.close();
		}
	}

	public synchronized void setPlayingPlaylist(int id, int val) {
		ContentValues values = new ContentValues();
		values.put("is_playing", val);

		mSQLiteDb.update(TABLE_PLAYLIST, values, "playlist_id=?",
				new String[] { String.valueOf(id) });
		if (val == 1) {
			// reset all other fields to 0
			values = new ContentValues();
			values.put("is_playing", 0);
			mSQLiteDb.update(TABLE_PLAYLIST, values, "playlist_id!=?",
					new String[] { String.valueOf(id) });
		}
	}

	public synchronized void setPlayingSongIdInPlaylist(int playId, int songId) {
		ContentValues values = new ContentValues();
		values.put("playing_song_id", songId);

		mSQLiteDb.update(TABLE_PLAYLIST, values, "playlist_id=?",
				new String[] { String.valueOf(playId) });
	}

	public synchronized int getPlayingSongIdInPlaylist(int playId) {
		String query = "select playing_song_id from " + TABLE_PLAYLIST
				+ " where playlist_id=" + playId;
		Cursor cursor = null;
		int val = -1;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {

				if (cursor.moveToFirst()) {
					val = cursor.getInt(0);
				}
			}
			return val;
		} catch (Exception e) {
			return val;
		} finally {
			if (cursor != null)
				cursor.close();
		}
	}

	public synchronized void resetPlayingPlaylist() {
		ContentValues values = new ContentValues();
		values.put("is_playing", 0);

		mSQLiteDb.update(TABLE_PLAYLIST, values, "1", null);
	}

	public synchronized void addSongToPlaylist(int id, List<Song> songs) {
		ContentValues values = new ContentValues();

		// add song to current playlist
		for (Song song : songs) {
			values = new ContentValues();
			values.put("playlist_id", id);
			values.put("song_id", song.getId());
			mSQLiteDb.insert(TABLE_PLAYLIST_DETAIL, null, values);
		}
	}

	public synchronized List<TwoLineItem> getAllPlayList() {
		String query = "select tb_playlist.playlist_id, tb_playlist.playlist_name, count(*) from "
				+ TABLE_PLAYLIST
				+ " inner join "
				+ TABLE_PLAYLIST_DETAIL
				+ " on tb_playlist.playlist_id = tb_playlist_detail.playlist_id"
				+ " group by tb_playlist_detail.playlist_id order by tb_playlist.playlist_name asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<TwoLineItem> list = new ArrayList<TwoLineItem>();
					TwoLineItem item;
					do {
						item = new TwoLineItem();
						item.id = cursor.getInt(0);
						item.name = cursor.getString(1);
						item.count = cursor.getInt(2);
						list.add(item);
					} while (cursor.moveToNext());
					return list;
				}
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<TwoLineItem>();
	}

	public synchronized List<TwoLineItem> getAllSongFolder() {
		String query = "select song_folder, count(*) from " + TABLE_SONGS
				+ " group by song_folder order by song_folder asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<TwoLineItem> list = new ArrayList<TwoLineItem>();
					TwoLineItem item;
					do {
						item = new TwoLineItem();
						item.name = cursor.getString(0);
						item.count = cursor.getInt(1);
						list.add(item);
					} while (cursor.moveToNext());
					return list;
				}
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<TwoLineItem>();
	}

	public synchronized void savePlayingSong(List<Song> songs) {
		if (songs == null)
			return;
		// Delete all data before saving new list
		mSQLiteDb.delete(TABLE_SONGS_SAVED, "1", null);
		for (Song song : songs) {
			ContentValues values = new ContentValues();
			values.put("song_id", song.getId());

			// Inserting Row
			mSQLiteDb.insert(TABLE_SONGS_SAVED, null, values);
		}
	}

	public synchronized void deletePlaylist(int playlistId) {
		List<Song> temp = getSongByPlaylist(playlistId);
		deletePlaylistDetail(temp);
		mSQLiteDb.delete(TABLE_PLAYLIST, "playlist_id=?",
				new String[] { String.valueOf(playlistId) });
	}

	public synchronized void deleteLibSongs(List<Song> songs) {
		deleteSongs(songs);
		deletePlayingSong(songs);
		deletePlaylistDetail(songs);
		deleteAlbumArt(songs);
		deleteLyrics(songs);
	}

	private synchronized void deleteSongs(List<Song> songs) {
		if (songs == null || songs.size() <= 0)
			return;

		mSQLiteDb.delete(TABLE_SONGS,
				"song_id in (" + createInClause(songs.size()) + ")",
				createSongIdSelectionArgs(songs));
	}

	private synchronized void deletePlayingSong(List<Song> songs) {
		if (songs == null || songs.size() <= 0)
			return;

		mSQLiteDb.delete(TABLE_SONGS_SAVED, "song_id in ("
				+ createInClause(songs.size()) + ")",
				createSongIdSelectionArgs(songs));
	}

	private synchronized void deletePlaylistDetail(List<Song> songs) {
		if (songs == null || songs.size() <= 0)
			return;

		mSQLiteDb.delete(TABLE_PLAYLIST_DETAIL, "song_id in ("
				+ createInClause(songs.size()) + ")",
				createSongIdSelectionArgs(songs));
	}

	private synchronized void deleteLyrics(List<Song> songs) {
		if (songs == null || songs.size() <= 0)
			return;

		mSQLiteDb.delete(TABLE_LYRICS,
				"song_id in (" + createInClause(songs.size()) + ")",
				createSongIdSelectionArgs(songs));
	}

	private synchronized void deleteAlbumArt(List<Song> songs) {
		if (songs == null || songs.size() <= 0)
			return;

		mSQLiteDb.delete(TABLE_ALBUM_ART,
				"song_id in (" + createInClause(songs.size()) + ")",
				createSongIdSelectionArgs(songs));
	}

	private synchronized String createInClause(int length) {
		if (length < 1)
			return "";
		StringBuilder sb = new StringBuilder(length * 2 - 1);
		sb.append("?");
		for (int i = 1; i < length; i++) {
			sb.append(",?");
		}
		return sb.toString();
	}

	private synchronized String[] createSongIdSelectionArgs(List<Song> songs) {
		String[] re = new String[songs.size()];
		int i = 0;
		for (Song s : songs) {
			re[i] = s.getId() + "";
			i++;
		}
		return re;
	}

	public synchronized List<Song> getSavedPlayingSong() {
		String query = "select tb_song.song_id, song_address,song_title,"
				+ "song_album,song_artist, song_genre, song_bitrate,"
				+ " is_favorite, played_time,song_duration, effect_id from "
				+ TABLE_SONGS + " inner join " + TABLE_SONGS_SAVED
				+ " on tb_song.song_id=tb_saved_playing_song.song_id "
				+ " order by song_title asc";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<Song> songs = new ArrayList<Song>();
					Song s = null;
					do {
						boolean isFav = cursor.getInt(7) == 1 ? true : false;

						s = new Song(mContext, cursor.getInt(0),
								cursor.getString(1), cursor.getString(2),
								cursor.getString(3), cursor.getString(4),
								cursor.getString(5), cursor.getInt(6), isFav,
								cursor.getInt(8), cursor.getInt(9),
								cursor.getInt(10));
						songs.add(s);
					} while (cursor.moveToNext());
					return songs;
				}
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<Song>();
	}

	public synchronized void deleteSavedPlayingSong() {
		mSQLiteDb.delete(TABLE_SONGS_SAVED, "1", null);
	}

	// //////////////////////////////////////////////
	// Skin
	// ////////////////////////////////////////////
	public synchronized int addSkinInfo(String name, String author,
			String address, boolean selected) {
		if (isSkinExist(name, author)) {
			return -1;
		}
		ContentValues values = new ContentValues();
		values.put("name", name);
		values.put("author", author);
		values.put("address", address);
		int isSel = selected == true ? 1 : 0;
		values.put("is_selected", isSel);

		// Inserting Row
		int id = (int) mSQLiteDb.insert(TABLE_SKIN, null, values);
		return id;
	}

	private synchronized boolean isSkinExist(String name, String author) {
		String query = "select * from " + TABLE_SKIN + " where name = '" + name
				+ "' and author='" + author + "'";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null && cursor.getCount() > 0) {
				return true;
			}
		} catch (Exception e) {
			return false;
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return false;
	}

	public synchronized void setSelectedSkin(int id) {
		ContentValues values = new ContentValues();
		values.put("is_selected", 1);

		mSQLiteDb.update(TABLE_SKIN, values, "id=?",
				new String[] { String.valueOf(id) });
		// reset all other fields to 0
		values = new ContentValues();
		values.put("is_selected", 0);
		mSQLiteDb.update(TABLE_SKIN, values, "id!=?",
				new String[] { String.valueOf(id) });
	}

	public synchronized List<SkinItem> getAllSkin() {
		String query = "select * from " + TABLE_SKIN;
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<SkinItem> list = new ArrayList<SkinItem>();
					SkinItem item;
					do {
						item = new SkinItem();
						item.id = cursor.getInt(0);
						item.name = cursor.getString(1);
						item.author = cursor.getString(2);
						item.address = cursor.getString(3);
						item.isSelected = cursor.getInt(4);
						list.add(item);
					} while (cursor.moveToNext());
					return list;
				}
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<SkinItem>();
	}

	public synchronized SkinItem getSelectedSkin() {
		String query = "select * from " + TABLE_SKIN + " "
				+ " where is_selected = 1";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					SkinItem item = new SkinItem();
					item.id = cursor.getInt(0);
					item.name = cursor.getString(1);
					item.author = cursor.getString(2);
					item.address = cursor.getString(3);
					item.isSelected = cursor.getInt(4);
					return item;
				}
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return null;
	}

	public synchronized void dropSkinTable() {
		mSQLiteDb.delete(TABLE_SKIN, "1", null);

		mSQLiteDb.delete("sqlite_sequence", "name=?",
				new String[] { TABLE_SKIN });

	}

	// //////////////////////////////////////////////
	// Theme
	// ////////////////////////////////////////////
	public synchronized long addThemeInfo(String name, String author,
			String address, boolean selected) {
		if (isThemeExist(name, author)) {
			return -1;
		}
		ContentValues values = new ContentValues();
		values.put("name", name);
		values.put("author", author);
		values.put("address", address);
		int isSel = selected == true ? 1 : 0;
		values.put("is_selected", isSel);

		// Inserting Row
		long id = mSQLiteDb.insert(TABLE_THEME, null, values);
		return id;

	}

	private synchronized boolean isThemeExist(String name, String author) {
		String query = "select * from " + TABLE_THEME + " where name = '"
				+ name + "' and author='" + author + "'";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null && cursor.getCount() > 0) {
				return true;
			}
		} catch (Exception e) {
			return false;
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return false;
	}

	public synchronized void setSelectedTheme(int id) {

		ContentValues values = new ContentValues();
		values.put("is_selected", 1);

		mSQLiteDb.update(TABLE_THEME, values, "id=?",
				new String[] { String.valueOf(id) });
		// reset all other fields to 0
		values = new ContentValues();
		values.put("is_selected", 0);
		mSQLiteDb.update(TABLE_THEME, values, "id!=?",
				new String[] { String.valueOf(id) });

	}

	public synchronized List<ThemeItem> getAllTheme() {
		String query = "select * from " + TABLE_THEME;
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<ThemeItem> list = new ArrayList<ThemeItem>();
					ThemeItem item;
					do {
						item = new ThemeItem();
						item.id = cursor.getInt(0);
						item.name = cursor.getString(1);
						item.author = cursor.getString(2);
						item.address = cursor.getString(3);
						item.isSelected = cursor.getInt(4);
						list.add(item);
					} while (cursor.moveToNext());
					return list;
				}
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<ThemeItem>();
	}

	public synchronized ThemeItem getSelectedTheme() {
		String query = "select * from " + TABLE_THEME + " "
				+ " where is_selected = 1";
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					ThemeItem item = new ThemeItem();
					item.id = cursor.getInt(0);
					item.name = cursor.getString(1);
					item.author = cursor.getString(2);
					item.address = cursor.getString(3);
					item.isSelected = cursor.getInt(4);

					return item;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return null;
	}

	public synchronized void dropThemeTable() {
		mSQLiteDb.delete(TABLE_THEME, "1", null);

		// delete sqlite_sequence table
		mSQLiteDb.delete("sqlite_sequence", "name=?",
				new String[] { TABLE_THEME });
	}

	// //////////////////////////////////////////////
	// Audio Effect
	// ////////////////////////////////////////////

	public synchronized AudioEffect getAudioEffect(int id) {
		String query = "select * from " + TABLE_AUDIO_EFFECT + " "
				+ " where id = " + id;
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					AudioEffect ae = new AudioEffect();
					ae.setId(cursor.getInt(0));
					ae.setName(cursor.getString(1));
					ae.setEq60(cursor.getInt(2));
					ae.setEq230(cursor.getInt(3));
					ae.setEq910(cursor.getInt(4));
					ae.setEq3k6(cursor.getInt(5));
					ae.setEq14k(cursor.getInt(6));
					ae.setBassboostStrength(cursor.getInt(7));
					ae.setVolume(cursor.getInt(8));
					ae.setBalance(cursor.getInt(9));
					boolean iseq = cursor.getInt(10) == 0 ? false : true;
					ae.setEqEnable(iseq);
					boolean isbass = cursor.getInt(11) == 0 ? false : true;
					ae.setBassboostEnable(isbass);
					ApplyMode am = cursor.getInt(12) == 0 ? ApplyMode.ALL_SONG
							: (cursor.getInt(12) == 1 ? ApplyMode.CURRENT_GROUP
									: ApplyMode.CURRENT_SONG);
					ae.setApplyMode(am);
					return ae;
				}
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new AudioEffect();
	}

	public synchronized List<AudioEffect> getPresets() {
		String query = "select * from " + TABLE_AUDIO_EFFECT
				+ " where can_delete_when_apply_all= " + 0;
		Cursor cursor = null;
		try {
			cursor = mSQLiteDb.rawQuery(query, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					List<AudioEffect> list = new ArrayList<AudioEffect>();
					AudioEffect ae;
					do {
						ae = new AudioEffect();
						ae.setId(cursor.getInt(0));
						ae.setName(cursor.getString(1));
						ae.setEq60(cursor.getInt(2));
						ae.setEq230(cursor.getInt(3));
						ae.setEq910(cursor.getInt(4));
						ae.setEq3k6(cursor.getInt(5));
						ae.setEq14k(cursor.getInt(6));
						ae.setBassboostStrength(cursor.getInt(7));
						ae.setVolume(cursor.getInt(8));
						ae.setBalance(cursor.getInt(9));
						boolean iseq = cursor.getInt(10) == 0 ? false : true;
						ae.setEqEnable(iseq);
						boolean isbass = cursor.getInt(11) == 0 ? false : true;
						ae.setBassboostEnable(isbass);
						ApplyMode am = cursor.getInt(12) == 0 ? ApplyMode.ALL_SONG
								: (cursor.getInt(12) == 1 ? ApplyMode.CURRENT_GROUP
										: ApplyMode.CURRENT_SONG);
						ae.setApplyMode(am);
						list.add(ae);
					} while (cursor.moveToNext());
					return list;
				}
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null)
				cursor.close();
		}
		return new ArrayList<AudioEffect>();
	}

	public synchronized int AddAudioEffect(AudioEffect item,
			int canDeleteWhenApplyAll) {

		ContentValues values = new ContentValues();
		values.put("name", item.getName());
		values.put("eq_60", item.getEq60());
		values.put("eq_230", item.getEq230());
		values.put("eq_910", item.getEq910());
		values.put("eq_3K6", item.getEq3k6());
		values.put("eq_14K", item.getEq14k());
		values.put("bass_strength", item.getBassboostStrength());
		values.put("volume", item.getVolume());
		values.put("balance", item.getBalance());
		values.put("is_eq", item.isEqEnable());
		values.put("is_bass", item.isBassboostEnable());
		values.put("can_delete_when_apply_all", canDeleteWhenApplyAll);
		int am = item.getApplyMode().ordinal();
		values.put("am", am);

		mSQLiteDb.insert(TABLE_AUDIO_EFFECT, null, values);

		// Get the Id
		String query = "select seq from sqlite_sequence where name=" + "'"
				+ TABLE_AUDIO_EFFECT + "'";
		Cursor cursor = mSQLiteDb.rawQuery(query, null);
		int EId = -1;
		if (cursor != null) {

			if (cursor.moveToFirst()) {
				EId = cursor.getInt(0);
			}
		}
		return EId;
	}

	public synchronized void updateAudioEffect(AudioEffect item,
			int canDeleteWhenApplyAll) {

		ContentValues values = new ContentValues();
		values.put("name", item.getName());
		values.put("eq_60", item.getEq60());
		values.put("eq_230", item.getEq230());
		values.put("eq_910", item.getEq910());
		values.put("eq_3K6", item.getEq3k6());
		values.put("eq_14K", item.getEq14k());
		values.put("bass_strength", item.getBassboostStrength());
		values.put("volume", item.getVolume());
		values.put("balance", item.getBalance());
		values.put("is_eq", item.isEqEnable());
		values.put("is_bass", item.isBassboostEnable());
		values.put("am", item.getApplyMode().ordinal());
		values.put("can_delete_when_apply_all", canDeleteWhenApplyAll);

		mSQLiteDb.update(TABLE_AUDIO_EFFECT, values, "id=?",
				new String[] { String.valueOf(item.getId()) });

	}

	public synchronized void setAudioEffectForSong(int songId, int effectId) {

		ContentValues values = new ContentValues();
		values.put("effect_id", effectId);

		mSQLiteDb.update(TABLE_SONGS, values, "song_id=?",
				new String[] { String.valueOf(songId) });
	}

	public synchronized void setAudioEffectForGroup(List<Integer> songIds,
			int effectId) {

		ContentValues values;
		for (Integer songId : songIds) {
			values = new ContentValues();
			values.put("effect_id", effectId);

			mSQLiteDb.update(TABLE_SONGS, values, "song_id=?",
					new String[] { String.valueOf(songId) });
		}

	}

	public synchronized void deleteAllCustomEffect() {
		try {
			mSQLiteDb.delete(TABLE_AUDIO_EFFECT, "can_delete_when_apply_all=?",
					new String[] { String.valueOf("1") });

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public synchronized void importBuiltInPreset() {
		// drop all db
		try {
			mSQLiteDb.delete(TABLE_AUDIO_EFFECT, "1", null);
		} catch (Exception e) {
			mSQLiteDb.execSQL("DROP TABLE IF EXISTS " + TABLE_AUDIO_EFFECT);
			// Create table audio effect
			String sql = "CREATE TABLE "
					+ TABLE_AUDIO_EFFECT
					+ "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
					+ "name NVARCHAR(50), eq_60 INTEGER, eq_230 INTEGER,"
					+ "eq_910 INTEGER,eq_3K6 INTEGER,eq_14K INTEGER,bass_strength INTEGER,"
					+ "volume INTEGER, balance INTEGER, is_eq INTEGER, is_bass INTEGER, am INTEGER,"
					+ "can_delete_when_apply_all INTEGER)";
			mSQLiteDb.execSQL(sql);
		}

		AudioEffect item = new AudioEffect();
		item.setBalance(50);
		item.setBassboostEnable(true);
		item.setBassboostStrength(1000);
		item.setEq60(-800 + 1500);
		item.setEq230(960 + 1500);
		item.setEq910(190 + 1500);
		item.setEq3k6(-460 + 1500);
		item.setEq14k(-1120 + 1500);
		item.setEqEnable(true);
		item.setName("Full Bass");
		item.setVolume(1000);
		item.setApplyMode(ApplyMode.CURRENT_SONG);
		AddAudioEffect(item, 0);

		item = new AudioEffect();
		item.setBalance(50);
		item.setBassboostEnable(true);
		item.setBassboostStrength(500);
		item.setEq60(710 + 1500);
		item.setEq230(350 + 1500);
		item.setEq910(-480 + 1500);
		item.setEq3k6(-520 + 1500);
		item.setEq14k(1200 + 1500);
		item.setEqEnable(true);
		item.setName("Bass and Trebel");
		item.setVolume(1000);
		item.setApplyMode(ApplyMode.CURRENT_SONG);
		AddAudioEffect(item, 0);

		item = new AudioEffect();
		item.setBalance(50);
		item.setBassboostEnable(true);
		item.setBassboostStrength(500);
		item.setEq60(-970 + 1500);
		item.setEq230(-970 + 1500);
		item.setEq910(180 + 1500);
		item.setEq3k6(1150 + 1500);
		item.setEq14k(1500 + 1500);
		item.setEqEnable(true);
		item.setName("Full Trebel");
		item.setVolume(1000);
		item.setApplyMode(ApplyMode.CURRENT_SONG);
		AddAudioEffect(item, 0);

		item = new AudioEffect();
		item.setBalance(50);
		item.setBassboostEnable(true);
		item.setBassboostStrength(0);
		item.setEq60(0 + 1500);
		item.setEq230(0 + 1500);
		item.setEq910(0 + 1500);
		item.setEq3k6(-100 + 1500);
		item.setEq14k(-720 + 1500);
		item.setEqEnable(true);
		item.setName("Classical");
		item.setVolume(1000);
		item.setApplyMode(ApplyMode.CURRENT_SONG);
		AddAudioEffect(item, 0);

		item = new AudioEffect();
		item.setBalance(50);
		item.setBassboostEnable(true);
		item.setBassboostStrength(500);
		item.setEq60(960 + 1500);
		item.setEq230(500 + 1500);
		item.setEq910(0 + 1500);
		item.setEq3k6(-575 + 1500);
		item.setEq14k(0 + 1500);
		item.setEqEnable(true);
		item.setName("Dance");
		item.setVolume(1000);
		item.setApplyMode(ApplyMode.CURRENT_SONG);
		AddAudioEffect(item, 0);

		item = new AudioEffect();
		item.setBalance(50);
		item.setBassboostEnable(true);
		item.setBassboostStrength(0);
		item.setEq60(480 + 1500);
		item.setEq230(900 + 1500);
		item.setEq910(-280 + 1500);
		item.setEq3k6(190 + 1500);
		item.setEq14k(1280 + 1500);
		item.setEqEnable(true);
		item.setName("Headphones");
		item.setVolume(1000);
		item.setApplyMode(ApplyMode.CURRENT_SONG);
		AddAudioEffect(item, 0);

		item = new AudioEffect();
		item.setBalance(50);
		item.setBassboostEnable(true);
		item.setBassboostStrength(0);
		item.setEq60(-490 + 1500);
		item.setEq230(290 + 1500);
		item.setEq910(550 + 1500);
		item.setEq3k6(550 + 1500);
		item.setEq14k(240 + 1500);
		item.setEqEnable(true);
		item.setName("Live");
		item.setVolume(1000);
		item.setApplyMode(ApplyMode.CURRENT_SONG);
		AddAudioEffect(item, 0);

		item = new AudioEffect();
		item.setBalance(50);
		item.setBassboostEnable(true);
		item.setBassboostStrength(100);
		item.setEq60(-170 + 1500);
		item.setEq230(560 + 1500);
		item.setEq910(580 + 1500);
		item.setEq3k6(-50 + 1500);
		item.setEq14k(-170 + 1500);
		item.setEqEnable(true);
		item.setName("Pop");
		item.setVolume(1000);
		item.setApplyMode(ApplyMode.CURRENT_SONG);
		AddAudioEffect(item, 0);

		item = new AudioEffect();
		item.setBalance(50);
		item.setBassboostEnable(true);
		item.setBassboostStrength(0);
		item.setEq60(800 + 1500);
		item.setEq230(-30 + 1500);
		item.setEq910(-300 + 1500);
		item.setEq3k6(450 + 1500);
		item.setEq14k(1110 + 1500);
		item.setEqEnable(true);
		item.setName("Rock");
		item.setVolume(1000);
		item.setApplyMode(ApplyMode.CURRENT_SONG);
		AddAudioEffect(item, 0);

		item = new AudioEffect();
		item.setBalance(50);
		item.setBassboostEnable(true);
		item.setBassboostStrength(50);
		item.setEq60(480 + 1500);
		item.setEq230(70 + 1500);
		item.setEq910(-10 + 1500);
		item.setEq3k6(450 + 1500);
		item.setEq14k(1110 + 1500);
		item.setEqEnable(true);
		item.setName("Soft");
		item.setVolume(1000);
		item.setApplyMode(ApplyMode.CURRENT_SONG);
		AddAudioEffect(item, 0);

		item = new AudioEffect();
		item.setBalance(50);
		item.setBassboostEnable(true);
		item.setBassboostStrength(100);
		item.setEq60(800 + 1500);
		item.setEq230(235 + 1500);
		item.setEq910(-480 + 1500);
		item.setEq3k6(100 + 1500);
		item.setEq14k(960 + 1500);
		item.setEqEnable(true);
		item.setName("TechNo");
		item.setVolume(1000);
		item.setApplyMode(ApplyMode.CURRENT_SONG);
		AddAudioEffect(item, 0);
	}

	public synchronized void addAlbumArt(String songId, String address) {
		if (isAlbumArtExist(songId)) {
			// update
			updateAlbumArt(songId, address);
			return;
		}
		ContentValues values = new ContentValues();
		values.put("song_id", songId);
		values.put("address", address);
		int nextIndex = getNextAlbumIndexForSearching(songId) + 1;
		values.put("next_index", nextIndex);
		values.put("is_show", 1);

		// Inserting Row
		mSQLiteDb.insert(TABLE_ALBUM_ART, null, values);
	}

	public synchronized void updateAlbumArt(String songId, String address) {
		ContentValues values = new ContentValues();
		values.put("address", address);
		int nextIndex = getNextAlbumIndexForSearching(songId) + 1;
		values.put("next_index", nextIndex);

		// update Row
		mSQLiteDb.update(TABLE_ALBUM_ART, values, "song_id=?",
				new String[] { songId });
	}

	public synchronized void updateAlbumArtIndex(String songId) {
		ContentValues values = new ContentValues();
		int nextIndex = getNextAlbumIndexForSearching(songId) + 1;
		values.put("next_index", nextIndex);

		// update Row
		mSQLiteDb.update(TABLE_ALBUM_ART, values, "song_id=?",
				new String[] { songId });
	}

	public synchronized boolean isAlbumArtExist(String songId) {
		String sql = "select song_id from " + TABLE_ALBUM_ART
				+ " where song_id=?";
		Cursor c = null;
		try {
			c = mSQLiteDb.rawQuery(sql, new String[] { songId });
			if (c != null && c.getCount() > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			return false;
		} finally {
			if (c != null)
				c.close();
		}
	}

	public synchronized int getNextAlbumIndexForSearching(String songId) {
		String sql = "select next_index from " + TABLE_ALBUM_ART
				+ " where song_id=" + songId;
		Cursor c = null;
		try {
			c = mSQLiteDb.rawQuery(sql, null);
			if (c != null) {
				if (c.moveToFirst()) {
					return c.getInt(0);
				}
			}
			return 0;
		} catch (Exception e) {
			return 0;
		} finally {
			if (c != null)
				c.close();
		}
	}

	// public synchronized int updateNextAlbumIndexForSearching(SQLiteDatabase
	// db, int
	// songId) {
	// ContentValues values = new ContentValues();
	// int currentIndex = getNextAlbumIndexForSearching(db, songId);
	// values.put("next_index", );
	// db.update(TABLE_ALBUM_ART, values, whereClause, whereArgs)
	// }

	public synchronized String getAlbumArtPath(int songId) {
		String sql = "select address from " + TABLE_ALBUM_ART
				+ " where song_id=" + songId;
		Cursor c = null;
		try {
			c = mSQLiteDb.rawQuery(sql, null);
			if (c != null) {
				if (c.moveToFirst()) {
					return c.getString(0);
				}
			}
			return null;
		} catch (Exception e) {
			return null;
		} finally {
			if (c != null)
				c.close();
		}
	}

	public synchronized int deleteAlbumArt(int songId) {

		// Delete Row
		int row = mSQLiteDb.delete(TABLE_ALBUM_ART, "song_id=?",
				new String[] { String.valueOf(songId) });
		return row;
	}

	/**
	 * @param songId
	 * @param val
	 *            : 1 to showing, 0 to hide it
	 */
	public synchronized void setHideAlbumArt(int songId, int val) {

		if (!isAlbumArtExist(songId + "")) {
			return;
		}
		ContentValues values = new ContentValues();
		values.put("is_show", val);

		// update Row
		mSQLiteDb.update(TABLE_ALBUM_ART, values, "song_id=?",
				new String[] { String.valueOf(songId) });
	}

	/**
	 * @param songId
	 * @return 0: is hidden, 1: is shown
	 */
	public synchronized int isAlbumArtHidden(int songId) {
		String sql = "select is_show from " + TABLE_ALBUM_ART
				+ " where song_id=" + songId;
		Cursor c = null;
		try {
			c = mSQLiteDb.rawQuery(sql, null);
			if (c != null) {
				if (c.moveToFirst()) {
					return c.getInt(0);
				}
			}
			return 1;
		} catch (Exception e) {
			return 1;
		} finally {
			if (c != null)
				c.close();
		}
	}

}
